package com.houdoudou.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.houdoudou.bean.Financer;
import com.houdoudou.bean.Machine;

public class MachineDao {

	 Connection conn=null;
	   
		public MachineDao(Connection conn) {
			super();
			this.conn = conn;
		}
		
		public  int create( Machine machine ) throws SQLException{	
			int result=0;
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "insert  into machine (secret,owner,boxNum,boxVolume,name,address,search,longitude,latitude,"
		        			+ "tempCfg,timeCfg,timeAEM,timeAAM,timeTLM,timeRLM,timeAEN,timeAAN,timeTLN,timeRLN,timeAEE,timeAAE,timeTLE,timeRLE,inuse,online,failure) "
		        			+ "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		        	pstm=conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
		        	pstm.setString(1, machine.getSecret());
		        	pstm.setString(2, machine.getOwner());
		        	pstm.setInt(3, machine.getBoxNum());
		        	pstm.setInt(4, machine.getBoxVolume());
		        	pstm.setString(5, machine.getName());
		        	pstm.setString(6, machine.getAddress());
		        	pstm.setString(7, machine.getSearch());
		        	pstm.setDouble(8, machine.getLongitude());
		        	pstm.setDouble(9, machine.getLatitude());
		        	pstm.setString(10, machine.getTempCfg());
		        	pstm.setString(11, machine.getTimeCfg());
		        	pstm.setString(12, machine.getTimeAEM());
		        	pstm.setString(13, machine.getTimeAAM());
		        	pstm.setString(14, machine.getTimeTLM());
		        	pstm.setString(15, machine.getTimeRLM());
		        	pstm.setString(16, machine.getTimeAEN());
		        	pstm.setString(17, machine.getTimeAAN());
		        	pstm.setString(18, machine.getTimeTLN());
		        	pstm.setString(19, machine.getTimeRLN());
		        	pstm.setString(20, machine.getTimeAEE());
		        	pstm.setString(21, machine.getTimeAAE());
		        	pstm.setString(22, machine.getTimeTLE());
		        	pstm.setString(23, machine.getTimeRLE());
		        	pstm.setInt(24, machine.getInuse());
		        	pstm.setInt(25, machine.getOnline());
		        	pstm.setInt(26, machine.getFailure());
		        
					pstm.executeUpdate();
					
					rs=pstm.getGeneratedKeys();
					rs.next();
					result=rs.getInt(1);
			
					
			   		
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return result;
		}
		
		public  void delete( int id) throws SQLException{	
			
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "delete  from machine where id="+id+"";		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
					stm.executeUpdate(sql);
					
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			
		}
		
		public  int update( Machine machine ) throws SQLException{	
			int result=0;
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "update machine set secret=?,owner=?,boxNum=?,boxVolume=?,name=?,address=?,search=?,longitude=?,latitude=?,"
		        			+ "tempCfg=?,timeCfg=?,timeAEM=?,timeAAM=?,timeTLM=?,timeRLM=?,timeAEN=?,timeAAN=?,timeTLN=?,timeRLN=?,timeAEE=?,timeAAE=?,timeTLE=?,timeRLE=?,inuse=?,online=?,failure=?  where id = ?";
		        	pstm=conn.prepareStatement(sql);	        	
		        	pstm.setString(1, machine.getSecret());
		        	pstm.setString(2, machine.getOwner());
		        	pstm.setInt(3, machine.getBoxNum());
		        	pstm.setInt(4, machine.getBoxVolume());
		        	pstm.setString(5, machine.getName());
		        	pstm.setString(6, machine.getAddress());
		        	pstm.setString(7, machine.getSearch());
		        	pstm.setDouble(8, machine.getLongitude());
		        	pstm.setDouble(9, machine.getLatitude());
		        	pstm.setString(10, machine.getTempCfg());
		        	pstm.setString(11, machine.getTimeCfg());
		        	pstm.setString(12, machine.getTimeAEM());
		        	pstm.setString(13, machine.getTimeAAM());
		        	pstm.setString(14, machine.getTimeTLM());
		        	pstm.setString(15, machine.getTimeRLM());
		        	pstm.setString(16, machine.getTimeAEN());
		        	pstm.setString(17, machine.getTimeAAN());
		        	pstm.setString(18, machine.getTimeTLN());
		        	pstm.setString(19, machine.getTimeRLN());
		        	pstm.setString(20, machine.getTimeAEE());
		        	pstm.setString(21, machine.getTimeAAE());
		        	pstm.setString(22, machine.getTimeTLE());
		        	pstm.setString(23, machine.getTimeRLE());
		        	pstm.setInt(24, machine.getInuse());
		        	pstm.setInt(25, machine.getOnline());
		        	pstm.setInt(26, machine.getFailure());
		        	pstm.setInt(27, machine.getId());
		        
		        
					result=pstm.executeUpdate();
					
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return result;
		}
		
		public  Machine selectById( int id) throws SQLException{	
			Machine machine = new Machine();
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "select * from machine where id="+id+"";		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
					rs=stm.executeQuery(sql);
					if(rs.next()){
						machine.setId(rs.getInt("id"));
						machine.setSecret(rs.getString("secret"));
						machine.setOwner(rs.getString("owner"));
						machine.setBoxNum(rs.getInt("boxNum"));
						machine.setBoxVolume(rs.getInt("boxVolume"));
						machine.setName(rs.getString("name"));
						machine.setAddress(rs.getString("address"));
						machine.setSearch(rs.getString("search"));
						machine.setLongitude(rs.getDouble("longitude"));
						machine.setLatitude(rs.getDouble("latitude"));
						machine.setOnline(rs.getInt("online"));
						machine.setInuse(rs.getInt("inuse"));
						machine.setFailure(rs.getInt("failure"));
						machine.setTimeCfg(rs.getString("timeCfg"));
						machine.setTempCfg(rs.getString("tempCfg"));
						machine.setTimeAEM(rs.getString("timeAEM"));
						machine.setTimeAAM(rs.getString("timeAAM"));
						machine.setTimeTLM(rs.getString("timeTLM"));
						machine.setTimeRLM(rs.getString("timeRLM"));
						machine.setTimeAEN(rs.getString("timeAEN"));
						machine.setTimeAAN(rs.getString("timeAAN"));
						machine.setTimeTLN(rs.getString("timeTLN"));
						machine.setTimeRLN(rs.getString("timeRLN"));
						machine.setTimeAEE(rs.getString("timeAEE"));
						machine.setTimeAAE(rs.getString("timeAAE"));
						machine.setTimeTLE(rs.getString("timeTLE"));
						machine.setTimeRLE(rs.getString("timeRLE"));
						
						System.out.println("bean in sql");
						Machine.printMachine(machine);
					}
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return machine;
		}
	
		public List< Machine> search( String sql) throws SQLException{	
			List< Machine> machines= new ArrayList<Machine>();
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
		
		        try {
		        		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
					rs=stm.executeQuery(sql);
					while(rs.next()){
						Machine machine = new Machine();
						machine.setId(rs.getInt("id"));
						machine.setSecret(rs.getString("secret"));
						machine.setOwner(rs.getString("owner"));
						machine.setBoxNum(rs.getInt("boxNum"));
						machine.setBoxVolume(rs.getInt("boxVolume"));
						machine.setName(rs.getString("name"));
						machine.setAddress(rs.getString("address"));
						machine.setSearch(rs.getString("search"));
						machine.setLongitude(rs.getDouble("longitude"));
						machine.setLatitude(rs.getDouble("latitude"));
						machine.setOnline(rs.getInt("online"));
						machine.setInuse(rs.getInt("inuse"));
						machine.setFailure(rs.getInt("failure"));
						machine.setTimeCfg(rs.getString("timeCfg"));
						machine.setTempCfg(rs.getString("tempCfg"));
						machine.setTimeAEM(rs.getString("timeAEM"));
						machine.setTimeAAM(rs.getString("timeAAM"));
						machine.setTimeTLM(rs.getString("timeTLM"));
						machine.setTimeRLM(rs.getString("timeRLM"));
						machine.setTimeAEN(rs.getString("timeAEN"));
						machine.setTimeAAN(rs.getString("timeAAN"));
						machine.setTimeTLN(rs.getString("timeTLN"));
						machine.setTimeRLN(rs.getString("timeRLN"));
						machine.setTimeAEE(rs.getString("timeAEE"));
						machine.setTimeAAE(rs.getString("timeAAE"));
						machine.setTimeTLE(rs.getString("timeTLE"));
						machine.setTimeRLE(rs.getString("timeRLE"));
						Machine.printMachine(machine);
						machines.add(machine);
					}
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return machines;
		}	
		
		public List< Machine> selectTest( ) throws SQLException{	
			List< Machine> machines= new ArrayList<Machine>();
			  PreparedStatement pstm=null; 
			   Statement  stm=null;
			   ResultSet rs=null; 	
			   String sql=null;
		        try {
		        	sql = "select * from machine where timeAAM > 0 ";		        	
					stm=conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
					rs=stm.executeQuery(sql);
					while(rs.next()){
						Machine machine = new Machine();
			
						machine.setId(rs.getInt("id"));
						machine.setSecret(rs.getString("secret"));
						machine.setOwner(rs.getString("owner"));
						machine.setBoxNum(rs.getInt("boxNum"));
						machine.setBoxVolume(rs.getInt("boxVolume"));
						machine.setName(rs.getString("name"));
						machine.setAddress(rs.getString("address"));
						machine.setSearch(rs.getString("search"));
						machine.setLongitude(rs.getDouble("longitude"));
						machine.setLatitude(rs.getDouble("latitude"));
						machine.setTimeCfg(rs.getString("timeCfg"));
						machine.setTempCfg(rs.getString("tempCfg"));
						machine.setTimeAEM(rs.getString("timeAEM"));
						machine.setTimeAAM(rs.getString("timeAAM"));
						machine.setTimeTLM(rs.getString("timeTLM"));
						machine.setTimeRLM(rs.getString("timeRLM"));
						machine.setTimeAEN(rs.getString("timeAEN"));
						machine.setTimeAAN(rs.getString("timeAAN"));
						machine.setTimeTLN(rs.getString("timeTLN"));
						machine.setTimeRLN(rs.getString("timeRLN"));
						machine.setTimeAEE(rs.getString("timeAEE"));
						machine.setTimeAAE(rs.getString("timeAAE"));
						machine.setTimeTLE(rs.getString("timeTLE"));
						machine.setTimeRLE(rs.getString("timeRLE"));
	                    Machine.printMachine(machine);
						machines.add(machine);
					}
				} finally{
					ConnFactory.closeStatment(pstm, pstm, rs);
				}			
			return machines;
		}	
}
